package org.cncitrus.data.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.cncitrus.data.common.Params;

public class UserConn {
	private Statement stmt;
	private String TABLE_NAME = "user";
	public UserConn(){
		try {
			stmt = OrangeConn.getInstance().getStatement();
		} catch (SQLException e) {
			System.err.print("UservaliateConn:无法实例化Statement");
			e.printStackTrace();
		}
	}
	public String load(){
		StringBuffer sb = new StringBuffer();
		String sql = "SELECT * FROM "+TABLE_NAME;
		try {
			ResultSet rs = stmt.executeQuery(sql);
			rs.beforeFirst();
			while(rs.next()){				
				sb.append("<form action=\"../ClientReq\" method=\"post\">");
				sb.append("<tr>");
				sb.append(" <td>"+rs.getString(Params.User.username)+"</td>");
				sb.append(" <td>"+rs.getString(Params.User.psw)+"</td>");
				sb.append("  <input name=\"atp\" type=\"hidden\" value=\"DeleteUser\" />");
				sb.append(" <input name=\"uid\" type=\"hidden\" value=\""+rs.getString(Params.User.uid)+"\" />");
				sb.append("<td><input name=\"\" type=\"submit\" value=\"删除\" /></td>");	
				sb.append("</tr>");
				sb.append("</form>");				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sb.toString();
	}
	public boolean login(String username,String psw) throws SQLException{
		boolean returnvalue = false;
		String sql = "SELECT * FROM "+TABLE_NAME+" WHERE "+Params.User.username +"=\""+username.trim()+"\" AND "+Params.User.psw+"=\""+psw.trim()+"\"";
		ResultSet rs = stmt.executeQuery(sql);
		rs.last();
		if(rs.getRow()>0)
			returnvalue = true;
		else
			returnvalue = false;
		return returnvalue;
	}
	public boolean insert(String username,String psw){
		boolean returnvalue = false;
		String sql = "INSERT INTO "+TABLE_NAME + " ("+Params.User.username+","+Params.User.psw+") VALUES (\""+username.replace("\"", "").trim()+"\",\""+psw+"\")";
		if(username.equals("")||psw.equals("")){
			returnvalue = false;
		}
		else{
			try {
				stmt.execute(sql);
				returnvalue = true;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return returnvalue;
	}
	public boolean update(String usr,String oldpsw,String newpsw){
		boolean returnvalue = false;
		String sql = "UPDATE "+TABLE_NAME+" SET "+Params.User.psw +"=\""+newpsw+"\" WHERE "+Params.User.username+"=\""+usr+"\" AND "+Params.User.psw+" = \""+oldpsw+"\"";
		try {
			returnvalue = stmt.executeUpdate(sql)>0;
//			returnvalue = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return returnvalue;
	}
	public boolean delete(String uid){
		boolean returnvalue = false;
		if(uid.equals("1"))//admin用户不能删除id为1
			return false;
		String sql = "DELETE  FROM "+TABLE_NAME+" WHERE "+Params.User.uid+"="+uid;
		try {
			stmt.execute(sql);
			returnvalue = true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return returnvalue;
	}
	

	
}
